package com.campfire.intelligentsecurity.business.service;

import cn.hutool.core.util.StrUtil;
import com.campfire.intelligentsecurity.business.bean.BusPerson;
import com.campfire.intelligentsecurity.business.bean.BusPersonWz;
import com.campfire.intelligentsecurity.sys.service.BaseService;
import com.github.drinkjava2.jsqlbox.DbContext;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpSession;

import java.sql.SQLException;

import static com.github.drinkjava2.jsqlbox.DB.par;

@Service
public class BusPersonService extends BaseService {
    @Autowired
    @Qualifier("main")
    DbContext dbContext;

    @Autowired
    HttpSession session;

    public Object List() {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT ");
        sb.append(" s.id, ");
        sb.append(" s.pname, ");
        sb.append(" s.sex, ");
        sb.append(" b1.enterprise_name unit, ");
        sb.append(" s.person_type personType, ");
        sb.append(" s.driver_type driverType, ");
        sb.append(" (select GROUP_CONCAT(CONCAT('',t.label,'') SEPARATOR ',')  FROM t_sys_dict t where t.value in (s.major_type) and t.type='dict_zylb') majorType, ");
        sb.append(" s.issue_date issueDate, ");
        sb.append(" s.driver_number driverNumber, ");
        sb.append(" s.face_id faceId, ");
        sb.append(" s.face_path facePath, ");
        sb.append(" s.face_relpath faceRelpath, ");
        sb.append(" s.face_abspath faceAbspath, ");
        sb.append(" s.driver_path driverPath, ");
        sb.append(" s.create_by createBy, ");
        sb.append(" s.create_time createTime, ");
        sb.append(" s.update_by updateBy, ");
        sb.append(" s.update_time updateTime, ");
        sb.append(" s.del_flag delFlag, ");
        sb.append(" s.remark, ");
        sb.append(" s.driver_point driverPoint, ");
        sb.append(" s.type, ");
        sb.append(" s.phone,");
        sb.append(" s.access_status accessStatus,");
        sb.append(" ifnull(c1.control_value, 1) black_flag,");
        sb.append(" ifnull(c2.control_value, 1) nelist_flag ");
        sb.append(" FROM");
        sb.append(" bus_person s");
        sb.append(" left join bus_unit b1 on b1.id= s.unit");
        sb.append(" left join t_control_record c1\n" +
                "                   on s.id = c1.relation_id and c1.relation_type = 0 and c1.control_type = 0 and c1.latest = 1");
        sb.append(" left join t_control_record c2\n" +
                "                   on s.id = c2.relation_id and c2.relation_type = 0 and c2.control_type = 1 and c2.latest = 1");
        sb.append(" WHERE 1=1 ");
        return builder(sb.toString());
    }

    public Object kfList() {
        return builder("SELECT * from bus_person_wz where 1=1 ");
    }

    public Object saveOrUpdate(BusPerson busPerson) {
        if (StrUtil.isNotEmpty(busPerson.getId())) {
            busPerson.setUpdateBy(session.getAttribute("usercode").toString());
            busPerson.update();
        } else {
            busPerson.setId(getUUID());
            busPerson.setCreateBy(session.getAttribute("usercode").toString());
            busPerson.insert();
        }
        return true;
    }

    public Object remove(String id) {
        return dbContext.exe("delete from bus_person where id in('" + id.replaceAll(",", "','") + "')");
    }

    public Object loadZylb(String id) {
        return dbContext.qryMapList("SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(b.major_type,',',h.help_topic_id+1),',',-1)\n" +
                " major_type FROM bus_person  b\n" +
                " JOIN mysql.help_topic h on h.help_topic_id<((LENGTH(b.major_type)-LENGTH(REPLACE(b.major_type,',',''))+1))\n" +
                " where b.id=? ",par(id));
    }

    public Object load(String id) {
        if (StrUtil.isNotEmpty(id)) {
            StringBuffer sb = new StringBuffer();
            sb.append(" SELECT");
            sb.append(" id, ");
            sb.append(" pname, ");
            sb.append(" sex, ");
            sb.append(" unit, ");
            sb.append(" person_type personType, ");
            sb.append(" driver_type driverType, ");
            sb.append(" major_type majorType, ");
            sb.append(" DATE_FORMAT(issue_date,'%Y-%m-%d') issueDate, ");
            sb.append(" driver_number driverNumber, ");
            sb.append(" face_id faceId, ");
            sb.append(" face_path facePath, ");
            sb.append(" face_relpath faceRelpath, ");
            sb.append(" face_abspath faceAbspath, ");
            sb.append(" driver_path driverPath, ");
            sb.append(" create_by createBy, ");
            sb.append(" create_time createTime, ");
            sb.append(" update_by updateBy, ");
            sb.append(" update_time updateTime, ");
            sb.append(" del_flag delFlag, ");
            sb.append(" remark, ");
            sb.append(" driver_point driverPoint, ");
            sb.append(" type, ");
            sb.append(" phone,");
            sb.append(" access_status accessStatus ");
            sb.append(" FROM");
            sb.append(" bus_person where id = ?");
            return dbContext.qryMap(sb.toString(), par(id));
        } else {
            return null;
        }
    }

    public Object loadWzPoint() {
        return dbContext.qryMapList("select id,info name,point from bus_wz_point where 1=1 order by CAST(point AS UNSIGNED) desc");
    }

    public Object koufen(BusPersonWz busPersonWz) {
        String[] personArr=busPersonWz.getPersonId().split(",");
        try {
            for (int i=0;i<personArr.length;i++){
                dbContext.exe("update bus_person set driver_point=(driver_point-?) where id=? ", par(busPersonWz.getWzPoint(), personArr[i]));
                dbContext.exe("INSERT INTO bus_person_wz( personId, pointInfo, wzPoint, wzImage, wzTime, pointId) VALUES ( ?, ?, ?, ?, ?, ?)", par(personArr[i],
                        busPersonWz.getPointInfo(),
                        busPersonWz.getWzPoint(),
                        busPersonWz.getWzImage(),
                        busPersonWz.getWzTime(),
                        busPersonWz.getPointId()));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
